#ifndef __DBMANAGER_HPP__
#define __DBMANAGER_HPP__

#include <string>
#include <mysql++/mysql++.h>
#include <ctime>

class DBManager {
public:
  std::string db;
  std::string host;
  std::string user;
  std::string pass;

private:
  mysqlpp::Connection conn;

  void begin_transaction(void)
  {
    //conn.query("BEGIN;").execute();
  }

  void commit_transaction(void)
  {
    //conn.query("COMMIT;").execute();
  }

  std::string escape_str(const std::string& str)
  {
    std::string result;
    for(size_t i = 0; i < str.length(); ++i) {
      switch( str[i] ) {
      case '\'':
	result += "\\'";
	break;
      case '"':
	result += "\\\"";
	break;
      default:
	result += str[i];
      }
    }
    return result;
  }
  
public:
  DBManager() : conn(false)
  {
  }

  bool db_connect(void)
  {
    bool con = conn.connect(db.c_str(), host.c_str(), user.c_str(), pass.c_str());
    con = (con && conn.query("USE allen;").execute());
    con = (con && conn.query("SET NAMES utf8;").execute());
    return con;
  }

  void db_disconnect(void)
  {
    conn.disconnect();
  }

  void clear_tables(void)
  {
    begin_transaction();
    conn.query("TRUNCATE movies;").execute();
    conn.query("TRUNCATE persons;").execute();
    conn.query("TRUNCATE cast;").execute();
    conn.query("TRUNCATE person_akas").execute();
    conn.query("TRUNCATE movie_akas;").execute();
    commit_transaction();
  }

  std::string add_movie(const std::string &title, const std::string &year, 
		 const std::string &media, const std::string &suspended)
  {
    std::string res = "";
    std::string f_year = (year == "65535" ? "NULL" : year);

    clock_t ts = clock();
    begin_transaction();
    mysqlpp::Query qr =  conn.query("INSERT INTO movies (mov_title, mov_year,\
 mov_media, mov_suspended) VALUES ('"+escape_str(title)+"',"+f_year+",'"+\
 media+"',"+suspended+");");

    if( qr.execute() ) {
      mysqlpp::StoreQueryResult qres = conn.query("SELECT LAST_INSERT_ID();").store();
      res = (std::string)qres[0][0];
    } else {
      std::cout << qr.error() << std::endl;
    }
    commit_transaction();
    clock_t te = clock();
    std::cout << "add_movie time: " << double(te-ts)/CLOCKS_PER_SEC << std::endl;
    return res;
  }

  void add_movie_aka(const std::string& aka, const std::string& movie)
  {
    clock_t ts = clock();
    begin_transaction();
    mysqlpp::Query qr = conn.query("INSERT INTO movie_akas (mov_id, aka)\
 VALUES ("+movie+",'"+escape_str(aka)+"');");
    if( !qr.execute() ) {
      std::cout << qr.error() << std::endl;
    }
    commit_transaction();
    clock_t te = clock();
    std::cout << "add_movie_aka time: " << double(te-ts)/CLOCKS_PER_SEC << std::endl;
  }

  void add_movie_genre(const std::string& genre, const std::string& movie)
  {
    clock_t ts = clock();
    begin_transaction();
    mysqlpp::Query qr = conn.query("UPDATE movies SET mov_genres = CONCAT_WS(',', mov_genres, '"+genre+"') WHERE mov_id = "+movie+";");
    if ( !qr.execute() ) {
      std::cout << qr.error() << std::endl;
    }
    commit_transaction();
    clock_t te = clock();
    std::cout << "add_movie_genre time: " << double(te-ts)/CLOCKS_PER_SEC << std::endl;
  }

  std::string add_person( const std::string& name )
  {
    std::string res = "";
    
    clock_t ts = clock();
    begin_transaction();
    mysqlpp::Query qr = conn.query("INSERT INTO persons (per_name) VALUES ('"+escape_str(name)+"');");
    if( qr.execute() ) {
      mysqlpp::StoreQueryResult qres = conn.query("SELECT LAST_INSERT_ID();").store();
      res = (std::string)qres[0][0];
    } else {
      std::cout << qr.error() << std::endl;
    }
    commit_transaction();
    clock_t te = clock();
    std::cout << "add_person time: " << double(te-ts)/CLOCKS_PER_SEC << std::endl;

    return res;
  }

  void add_person_aka(const std::string& aka, const std::string& person)
  {
    begin_transaction();
    clock_t ts = clock();
    mysqlpp::Query qr = conn.query("INSERT INTO person_akas (per_id, aka)\
 VALUES ("+person+",'"+escape_str(aka)+"');");
    if( !qr.execute() ) {
      std::cout << qr.error() << std::endl;
    }
    commit_transaction();
    clock_t te = clock();
    std::cout << "add_person_aka time: " << double(te-ts)/CLOCKS_PER_SEC << std::endl;
  }

  void add_link(const std::string& per_id, const std::string& mov_id, const std::string& role)
  {
    begin_transaction();
    clock_t ts = clock();
    mysqlpp::Query qr = conn.query("INSERT INTO cast (per_id, mov_id, per_mov_role)\
 VALUES ("+per_id+","+mov_id+",'"+role+"')");
    if ( !qr.execute() ) {
      std::cout << qr.error() << std::endl;
    }
    commit_transaction();
    clock_t te = clock();
    std::cout << "add_link time: " << double(te-ts)/CLOCKS_PER_SEC << std::endl;
  }

  std::string get_movie_id(const std::string& mov_title)
  {
    std::string res = "";
    clock_t ts = clock();
    begin_transaction();
    mysqlpp::Query qr = conn.query("SELECT mov_id FROM movies WHERE mov_title = '"+escape_str(mov_title)+"';");
    mysqlpp::StoreQueryResult qres = qr.store();
    if ( qres && qres.num_rows() == 1 ) {
      res = (std::string)qres[0][0];
    } else if ( !qres ) {
      std::cout << qr.error() << std::endl;
    }
    commit_transaction();
    clock_t te = clock();
    std::cout << "get_movie_id time: " << double(te-ts)/CLOCKS_PER_SEC << std::endl;
    return res;
  }
};

#endif
